﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using CP.Entities;
using CP.DBService;
using CP.Utility;

namespace CP.Repositories.GPM
{
    public class UserGroupRepository
    {
        DBHelper db = DBHelper.GPMCreateInstance();

        public IList<User_Group> GetAllUserGroups()
        {
            return db.ExecuteObject<User_Group>("select * from user_group order by group_id");
        }

        public User_Group GetGroupByID(int id)
        {
            IList<User_Group> groups = db.ExecuteObject<User_Group>(string.Format(@"select *
                                                        from user_group where group_id
                                                        ={0} ", id));
            if (groups.Count > 0)
                return groups[0];
            return null;
        }

        public IList<User_Group> GetGroupByMenuID(int menuid)
        {
            return db.ExecuteObject<User_Group>(string.Format(@"select t1.* from 
                                                user_group t1,menu_right t2
                                              where t1.group_id =t2.group_id 
                                                        and t2.menu_id = {0} 
                                                    order by t1.group_id", menuid));
        }

        public IList<User_Group> GetUnAuthGroupByMenuID(int menuid)
        {
            return db.ExecuteObject<User_Group>(string.Format(@"SELECT *
                                              FROM user_group t1
                                             WHERE NOT EXISTS (SELECT NULL
                                                 FROM menu_right t2
                                                WHERE t1.GROUP_ID = t2.GROUP_ID 
                                                  AND t2.menu_id = {0}) 
                                                order by t1.group_id", menuid));
        }

        public IList<User_Group> GetGroupByButtonID(int webpartid, int buttonid)
        {
            return db.ExecuteObject<User_Group>(string.Format(@"select t1.* from 
                                                user_group t1,webpart_right_button t2
                                              where t1.group_id =t2.group_id 
                                                and t2.webpart_id = {0} 
                                                and t2.button_id = {1}
                                              order by t1.group_id", webpartid, buttonid));
        }

        public IList<User_Group> GetUnAuthGroupByButtonID(int webpartid, int buttonid)
        {
            return db.ExecuteObject<User_Group>(string.Format(@"SELECT   *
                                    FROM user_group
                                   WHERE GROUP_ID NOT IN (
                                            SELECT t1.GROUP_ID
                                              FROM user_group t1, webpart_right_button t2
                                             WHERE t1.GROUP_ID = t2.GROUP_ID
                                               AND t2.webpart_id = {0}
                                               AND t2.button_id = {1})
                                ORDER BY GROUP_ID", webpartid, buttonid));
        }

        public IList<User_Group> GetGroupByUserID(string userid)
        {
            return db.ExecuteObject<User_Group>(string.Format(@"select t1.* from 
                                                user_group t1,user_group_relation t2
                                              where t1.group_id =t2.group_id 
                                                        and t2.user_id = '{0}'
                                                    order by t1.group_id", userid));
        }

        public bool InsertUserRole(string userid, int defaultRole, IList<int> groupids)
        {
            try
            {
                db.BeginTransaction();
                db.ExecuteNonQuery(string.Format(@" update user_info set group_id 
                                                    ={0} where user_id = '{1}' ",
                                                    defaultRole, userid));
                db.ExecuteNonQuery(string.Format(@" delete user_group_relation   
                                            where user_id = '{0}' ", userid));

                foreach (int id in groupids)
                {
                    db.ExecuteNonQuery(string.Format(@" insert into user_group_relation   
                                                    (user_id , group_id) 
                                                    values('{0}',{1})",
                                                        userid, id));
                }

                db.ExecuteNonQuery(string.Format(@" delete user_group_relation   
                                            where user_id = '{0}' and group_id={1}",
                                                    userid, defaultRole));
                db.ExecuteNonQuery(string.Format(@" insert into user_group_relation   
                                                    (user_id , group_id) 
                                                    values('{0}',{1})",
                                                    userid, defaultRole));
                db.Commit();
                return true;
            }
            catch (Exception ex)
            {
                db.Rollback();
                throw ex;
            }
        }

        public bool InsertRoleUser(int groupid, IList<string> userids)
        {
            try
            {
                db.BeginTransaction();

                db.ExecuteNonQuery(string.Format(@" delete user_group_relation   
                                            where group_id = '{0}' ", groupid));

                foreach (string id in userids)
                {
                    db.ExecuteNonQuery(string.Format(@" insert into user_group_relation   
                                                    (user_id , group_id) 
                                                    values('{0}',{1})",
                                                        id, groupid));
                }

                db.Commit();
                return true;
            }
            catch (Exception ex)
            {
                db.Rollback();
                throw ex;
            }
        }

        public bool DeleteGroups(IList<int> groupids)
        {
            try
            {
                db.BeginTransaction();
                foreach (int id in groupids)
                {
                    db.ExecuteNonQuery(string.Format(@"delete user_group 
                                                where group_id ={0}", id));
                    db.ExecuteNonQuery(string.Format(@"delete user_group_relation 
                                                where group_id ={0}", id));
                    db.ExecuteNonQuery(string.Format(@"delete WEBPART_RIGHT_LIST
                                                where group_id = {0}", id));
                    db.ExecuteNonQuery(string.Format(@"delete webpart_right_button
                                                where group_id = {0} ", id));
                    db.ExecuteNonQuery(string.Format(@"delete  MENU_RIGHT
                                                   WHERE GROUP_ID = {0}",
                                           id));
                    db.ExecuteNonQuery(string.Format(@"delete  Webpart_RIGHT
                                                   WHERE GROUP_ID = {0}",
                                                            id));
                }
                db.Commit();
                return true;
            }
            catch (Exception ex)
            {
                db.Rollback();
                throw ex;
            }

        }

        public bool AddUserGroup(User_Group group)
        {
            try
            {
                db.BeginTransaction();
                object ret = db.ExecuteScalar(string.Format(@"select count(*) from dual
                                            where exists(select null from 
                                                user_group where group_id = {0})",
                                                                group.group_id));
                int exist = Convert.ToInt16(ret);
                if (0 != exist)
                    throw new Exception("Group_ID=[" + group.group_id + "]已经存在");

                db.ExecuteNonQuery(string.Format(@"insert into user_group(group_id,
                                                 group_name,parent_id,remark) 
                                                values({0},'{1}',{2},'{3}')",
                                              group.group_id, group.group_name,
                                              group.parent_id, group.remark));

                if (group.parent_id != 0)
                {
                    ret = db.ExecuteScalar(string.Format(@"select count(*) from dual
                                        where exists(select null from 
                                            user_group where group_id = {0} )",
                                                                 group.parent_id));
                    exist = Convert.ToInt16(ret);
                    if (0 == exist)
                        throw new Exception("Parent_ID=[" + group.parent_id + "]不存在");

                    db.ExecuteNonQuery(string.Format(@"delete  MENU_RIGHT
                                                   WHERE GROUP_ID = {0}",
                       group.group_id));

                    db.ExecuteNonQuery(string.Format(@"INSERT INTO MENU_RIGHT
                                            (right_id,GROUP_ID, menu_id,right_flag)
                                      SELECT SEQ_MENU_RIGHT.NextVal right_id, {0} GROUP_ID, 
                                              menu_id,'R' right_flag
                                        FROM MENU_RIGHT 
                                        WHERE GROUP_ID = {1}",
                                                 group.group_id, group.parent_id));

                    db.ExecuteNonQuery(string.Format(@"delete  Webpart_RIGHT
                                                   WHERE GROUP_ID = {0}",
                                                            group.group_id));

                    db.ExecuteNonQuery(string.Format(@"INSERT INTO Webpart_RIGHT
                                            (right_id,GROUP_ID, webpart_id,right_flag)
                                      SELECT SEQ_WEBPART_RIGHT.NextVal right_id, {0} GROUP_ID, 
                                              webpart_id,'R' right_flag
                                        FROM Webpart_RIGHT 
                                        WHERE GROUP_ID = {1}",
                                                 group.group_id, group.parent_id));

                    db.ExecuteNonQuery(string.Format(@"delete  webpart_right_button
                                                   WHERE GROUP_ID = {0}",
                                            group.group_id));

                    db.ExecuteNonQuery(string.Format(@"INSERT INTO webpart_right_button
                                            (GROUP_ID, webpart_id, button_id, 
                                             action_id, button_name)
                                      SELECT {0} GROUP_ID, webpart_id, button_id, 
                                             action_id, button_name
                                        FROM webpart_right_button 
                                        WHERE GROUP_ID = {1}",
                                                 group.group_id, group.parent_id));

                    db.ExecuteNonQuery(string.Format(@"delete  WEBPART_RIGHT_LIST
                                                   WHERE GROUP_ID = {0}",
                            group.group_id));

                    db.ExecuteNonQuery(string.Format(@"INSERT INTO WEBPART_RIGHT_LIST
                                            (GROUP_ID, webpart_id, list_id, 
                                             field_name, list_name)
                                      SELECT {0} GROUP_ID, webpart_id, list_id, 
                                             field_name, list_name
                                        FROM WEBPART_RIGHT_LIST 
                                        WHERE GROUP_ID = {1}",
                                                 group.group_id, group.parent_id));
                }

                db.Commit();
                return true;
            }
            catch (Exception ex)
            {
                db.Rollback();
                throw ex;
            }
        }

        public bool UpdateUserGroup(User_Group group)
        {
            try
            {
                db.BeginTransaction();
                object ret = db.ExecuteScalar(string.Format(@"select count(*) from dual
                                            where exists(select null from 
                                                user_group where group_id = {0})",
                                                                group.group_id));
                int exist = Convert.ToInt16(ret);
                if (0 == exist)
                    throw new Exception("Group_ID=[" + group.group_id + "]不存在");
                ret = db.ExecuteScalar(string.Format(@"select count(*) from dual
                                        where exists(select null from 
                                            user_group where parent_id = {0}) ",
                                                             group.parent_id));
                exist = Convert.ToInt16(ret);
                if (0 == exist)
                    throw new Exception("Parent_ID=[" + group.parent_id + "]不存在");

                db.ExecuteNonQuery(string.Format(@"update user_group set  
                                                     group_name = '{1}',
                                                     parent_id = {2},
                                                     remark = '{3}'
                                                 where group_id = {0} ",
                                                group.group_id, group.group_name,
                                                group.parent_id, group.remark));

                User_Group oldgroup = GetGroupByID(group.group_id);
                if (oldgroup.parent_id != group.parent_id)
                {
                    db.ExecuteNonQuery(string.Format(@"delete  MENU_RIGHT
                                                   WHERE GROUP_ID = {0}",
                        group.group_id));

                    db.ExecuteNonQuery(string.Format(@"INSERT INTO MENU_RIGHT
                                            (right_id,GROUP_ID, menu_id,right_flag)
                                      SELECT SEQ_MENU_RIGHT.NextVal right_id, {0} GROUP_ID, 
                                              menu_id,'R' right_flag
                                        FROM MENU_RIGHT 
                                        WHERE GROUP_ID = {1}",
                                                 group.group_id, group.parent_id));

                    db.ExecuteNonQuery(string.Format(@"delete  Webpart_RIGHT
                                                   WHERE GROUP_ID = {0}",
                                                            group.group_id));

                    db.ExecuteNonQuery(string.Format(@"INSERT INTO Webpart_RIGHT
                                            (right_id,GROUP_ID, webpart_id,right_flag)
                                      SELECT SEQ_WEBPART_RIGHT.NextVal right_id, {0} GROUP_ID, 
                                              webpart_id,'R' right_flag
                                        FROM Webpart_RIGHT 
                                        WHERE GROUP_ID = {1}",
                                                 group.group_id, group.parent_id));

                    db.ExecuteNonQuery(string.Format(@"delete  webpart_right_button
                                                   WHERE GROUP_ID = {0}",
                                            group.group_id));

                    db.ExecuteNonQuery(string.Format(@"INSERT INTO webpart_right_button
                                            (GROUP_ID, webpart_id, button_id, 
                                             action_id, button_name)
                                      SELECT {0} GROUP_ID, webpart_id, button_id, 
                                             action_id, button_name
                                        FROM webpart_right_button 
                                        WHERE GROUP_ID = {1}",
                                                 group.group_id, group.parent_id));

                    db.ExecuteNonQuery(string.Format(@"delete  WEBPART_RIGHT_LIST
                                                   WHERE GROUP_ID = {0}",
                            group.group_id));

                    db.ExecuteNonQuery(string.Format(@"INSERT INTO WEBPART_RIGHT_LIST
                                            (GROUP_ID, webpart_id, list_id, 
                                             field_name, list_name)
                                      SELECT {0} GROUP_ID, webpart_id, list_id, 
                                             field_name, list_name
                                        FROM WEBPART_RIGHT_LIST 
                                        WHERE GROUP_ID = {1}",
                                                 group.group_id, group.parent_id));
                }

                db.Commit();
                return true;
            }
            catch (Exception ex)
            {
                db.Rollback();
                throw ex;
            }
        }
    }
}
